
DROP TABLE INTEX.QVW_KDSTAMM
GO
CREATE TABLE QVW_KDSTAMM
AS
SELECT DISTINCT  KDKUNDNR AS KDNR,KDKUNDNA AS KDSNAME,KDNAME1 AS KDNAME,KDLAND,KDORT
FROM KDSTAMM WHERE KDMANDANT='00'
GO
DROP TABLE INTEX.QVW_GSDAT
GO
CREATE TABLE QVW_GSDAT
AS
SELECT DISTINCT 'G' AS ARTST,GSART AS PART,GSVAR AS PVAR,GSART||GSVAR AS PFART,GSHANDLNR, GSGARNBEZ AS PARTBEZ,GSMAT,
GSNUMART AS GNUM,GSGARNNR AS GCOUNT,GSFACH AS GPLY,
GSPROGRP AS PRGR,GSARTGRP AS ARTGRP,
ROUND(dbms_random.value(20,40),2) AS STDCOST,
ROUND(dbms_random.value(20,40),2) AS EKCOST,
ROUND(dbms_random.value(20,40),2) AS PLANCOST
FROM GSDAT WHERE GSMANDANT='00'
GO
DROP TABLE INTEX.QVW_FWASTAMM
GO
CREATE TABLE QVW_FWASTAMM
AS
SELECT DISTINCT 'F' AS ARTST, WAART AS PART,WAVAR AS PVAR, WAART||WAVAR AS PFART,WABEZ AS PARTBEZ,
WAFDDIM AS DENU,WAWAART,WAFGRQM AS GRQM, WAFGRLFD AS RLFD,WAFBRTU as WIDTH,WAFFDDIK AS WDEN,WAFFDDIS AS FDEN,
WAPROGRP AS PRGR,WAARTGRP AS ARTGRP,
ROUND(dbms_random.value(20,40),2) AS STDCOST,
ROUND(dbms_random.value(20,40),2) AS EKCOST,
ROUND(dbms_random.value(20,40),2) AS PLANCOST
FROM WASTAMM WHERE WAMANDANT='00'
GO

DROP TABLE INTEX.QVW_GWASTAMM
GO
CREATE TABLE QVW_GWASTAMM
AS
SELECT DISTINCT  'R' AS ARTST, WAVWART AS PART,WAVWVAR AS PVAR, WAVWART||WAVWVAR AS PFART,WABEZ AS PARTBEZ,
WAFDDIM AS DENU,WAWAART,WARGRQM AS GRQM, WARGRLFD AS RLFD,WARWBRT as WIDTH,WARFDDIK AS WDEN,WARFDDIS AS FDEN,
WAPROGRP AS PRGR,WAARTGRP AS ARTGRP,
ROUND(dbms_random.value(20,40),2) AS STDCOST,
ROUND(dbms_random.value(20,40),2) AS EKCOST,
ROUND(dbms_random.value(20,40),2) AS PLANCOST
FROM WASTAMM WHERE WAMANDANT='00' AND SUBSTR(WAVWART,1,2)<>'  '
GO

DROP TABLE INTEX.QVW_BHSATZ
GO
CREATE TABLE QVW_BHSATZ
AS
SELECT DISTINCT  'K' AS ARTST,BHKART AS PART,BHKVAR AS PVAR, BHKART||BHKVAR AS PFART,
BSBEZ AS PARTBEZ,BSFDZ,
ROUND(dbms_random.value(20,40),2) AS STDCOST,
ROUND(dbms_random.value(20,40),2) AS EKCOST,
ROUND(dbms_random.value(20,40),2) AS PLANCOST
FROM BHSATZ JOIN BSSATZ ON BSMANDANT=BHMANDANT AND BSKART=BHKART
WHERE BHMANDANT='00'
GO
DROP TABLE INTEX.QVW_FSSTAMM
GO
CREATE TABLE QVW_FSSTAMM
AS
SELECT DISTINCT  'T' AS ARTST, FSART AS PART,FSVAR AS PVAR, FSART||FSVAR AS PFART,FSABEZ AS PARTBEZ,
FSMATGRP AS MATGRP,
FSPROGRP AS PRGR,FSARTGRP AS ARTGRP,
ROUND(dbms_random.value(20,40),2) AS STDCOST,
ROUND(dbms_random.value(20,40),2) AS EKCOST,
ROUND(dbms_random.value(20,40),2) AS PLANCOST
FROM FSSTAMM WHERE FSMANDANT='00'
GO

DROP TABLE INTEX.QVW_MSSTAMM
GO
CREATE TABLE QVW_MSSTAMM
AS
SELECT DISTINCT 'M' AS ARTST, MSMAT AS PART,MSVAR AS PVAR, MSMAT||MSVAR AS PFART,MSBEZ AS PARTBEZ,
ROUND(dbms_random.value(200,400),2) AS STDCOST,
ROUND(dbms_random.value(200,400),2) AS EKCOST,
ROUND(dbms_random.value(200,400),2) AS PLANCOST
FROM MSSTAMM WHERE MSMANDANT='00'
GO

DROP TABLE INTEX.QVW_VAART
GO
CREATE TABLE QVW_VAART
AS
SELECT  VAAKOLL||VAAART AS VART,VAABEZ AS VARTBEZ,VAAARTST AS ARTST,VAAPRGR AS PRGR,VAAARTGRP AS ARTGRP,
VAADART||VAADVAR AS PFART
FROM VAART WHERE VAAMANDANT='00'
GO
DROP TABLE INTEX.QVW_VAFARBEN
GO
CREATE TABLE QVW_VAFARBEN
AS
SELECT DISTINCT VAFKOLL,VAFART,VAFFARBE,VAFPGROE,
MAX(VAAARTST) AS ARTST,
VAFKOLL||VAFART AS VART,
VAFKOLL||VAFART||VAFFARBE AS VFARBE,
VAFKOLL||VAFART||VAFFARBE||VAFPGROE AS VASKU,
VAFBEZ AS VFBEZ,VAFPART||VAFPVAR AS PFART,ROUND( MAX(VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001))* dbms_random.value(0.7,1.5),2) AS PFCOST,VAFPART  AS PART,VAFPVAR  AS PVAR,VAFFFARBE AS PCOLOR,VAFDESS AS PDESS,VAFCOLORIT AS PDCWY, VAFBESCHICH AS PFINISH,VAFPGROE AS PSIZE,0.0 AS STDCOST,0.0 AS EKCOST,0.0 AS PLANCOST
FROM VAFARBEN F
JOIN VKPOS P ON  VAFKOLL=VKPKOLL AND   VKPART=VAFART AND VKPFARBE=VAFFARBE
JOIN VKKOPF ON VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD=VKPAUFLFD AND VKKMANDANT=VKPMANDANT
JOIN VAART ON VAAKOLL=VAFKOLL AND VAAART=VAFART
WHERE VAFMANDANT='00' AND VKKSA='  ' AND VKPSA='  '
AND VKPMEEHFKT<>'000000000000'
GROUP BY VAFMANDANT,VAFKOLL,VAFART,VAFFARBE,VAFPGROE,VAFBEZ,VAFPART,VAFPVAR,VAFDESS,VAFCOLORIT,VAFBESCHICH,VAFFFARBE

GO
UPDATE QVW_VAFARBEN F SET (PFCOST,STDCOST,EKCOST,PLANCOST)=(SELECT  MAX(STDCOST),MAX(STDCOST),MAX(EKCOST),MAX(PLANCOST)
 FROM QVW_FWASTAMM KS
WHERE  RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND F.PFART=KS.PFART)
WHERE EXISTS
(SELECT 1 FROM QVW_FWASTAMM KS WHERE   F.ARTST=KS.ARTST AND RTRIM(F.ARTST)=RTRIM(KS.ARTST))
GO

UPDATE QVW_VAFARBEN F SET  (PFCOST,STDCOST,EKCOST,PLANCOST)=(SELECT  MAX(STDCOST),MAX(STDCOST),MAX(EKCOST),MAX(PLANCOST)
 FROM QVW_GWASTAMM KS
WHERE   RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND F.PFART=KS.PFART)
WHERE EXISTS
(SELECT 1 FROM QVW_GWASTAMM KS WHERE   F.ARTST=KS.ARTST AND RTRIM(F.ARTST)=RTRIM(KS.ARTST))
GO

UPDATE QVW_VAFARBEN F SET (PFCOST,STDCOST,EKCOST,PLANCOST)=(SELECT  MAX(STDCOST),MAX(STDCOST),MAX(EKCOST),MAX(PLANCOST)
 FROM QVW_FSSTAMM KS
WHERE  RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND F.PFART=KS.PFART)
WHERE EXISTS
(SELECT 1 FROM QVW_FSSTAMM KS WHERE   F.ARTST=KS.ARTST AND RTRIM(F.ARTST)=RTRIM(KS.ARTST))
GO

DROP TABLE QVW_SD0231
GO
CREATE TABLE QVW_SD0231
AS
SELECT DISTINCT SUBSTR(SDKEY,3,15) AS KOPERATION, SUBSTR(SDDATEN,2,10) AS KOP FROM SDDAT
WHERE SDSA='0231' AND SUBSTR(SDKEY,1,2)='00'
GO
DROP TABLE QVW_LIEFDAT
GO
CREATE TABLE QVW_LIEFDAT
AS
SELECT DISTINCT LSLIEFNR AS LSNR,LSLIEFNA,LSNAME1 FROM LIEFDAT
WHERE  LSMANDANT='00'
GO

